Part 07 (실습): PostgreSQL 18 내부 구조(MVCC · Lock · pg_stat_statements)와 Terraform IaC 검증

실습 검증 포인트 한눈에 보기

flowchart TB
    subgraph STEP1_2["Step 1~2 · MVCC 물리 주소(ctid) 이동 및 VACUUM 청소"]
        direction LR
        INS["INSERT 1건
ctid: (0,1) | xmin: 757 | xmax: 0"] --> UPD["UPDATE 실행!
(0,1)은 Dead Tuple(n_dead_tup=1)이 되고
새 위치 ctid: (0,2)에 새 버전 생성!"]
        UPD --> VAC["VACUUM mesh_orders;
n_dead_tup: 2 -> 0 으로 즉시 청소!"]
    end
    subgraph STEP3_4["Step 3~4 · Row-Level Lock 경합 재현 및 범인 PID 색출"]
        direction LR
        TX1["세션 1 (PID 39)
BEGIN; SELECT ... FOR UPDATE;
6초간 슬립 (자물쇠 독점!)"] -.->|"blocked_by = {39}"| TX2["세션 2 (PID 40)
UPDATE ... WHERE id = 1;
wait_event: Lock / transactionid 멈춤!"]
        TX2 --> STAT["pg_stat_statements 조회:
Lock 대기로 인해 UPDATE가 4,021ms 기록됨!"]
    end

Step 1. pg_stat_statements 성능 블랙박스 활성화

먼저 외부 PostgreSQL 18.6 서버(study-pg-vm)에 느린 쿼리 추적기인 pg_stat_statements 라이브러리를 로드하도록 ALTER SYSTEM 명령을 내리고 컨테이너를 재시작합니다.

# 1. PostgreSQL 설정 파일(postgresql.auto.conf)에 pg_stat_statements 프리로드 등록
kubectl exec -n part1-demo pg-client -- psql -h pg-vm.mesh.internal -U meshuser -d ordersdb -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';"

# 2. 외부 DB VM 컨테이너 재시작 후 4초 대기
docker restart study-pg-vm
Start-Sleep -Seconds 4

# 3. ordersdb 데이터베이스에 pg_stat_statements 확장 생성!
kubectl exec -n part1-demo pg-client -- psql -h pg-vm.mesh.internal -U meshuser -d ordersdb -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

실제 출력 결과:

ALTER SYSTEM
study-pg-vm
CREATE EXTENSION

Step 2. MVCC 실측 — UPDATE 전후의 물리 주소(ctid) 이동과 VACUUM 청소!

이제 mesh_orders 테이블을 비우고(TRUNCATE), 1번 주문 데이터를 넣은(INSERT) 직후와 수정한(UPDATE) 직후에 숨겨진 시스템 컬럼 ctid(물리 위치), xmin(생성 트랜잭션), xmax(삭제 트랜잭션) 가 어떻게 바뀌는지 직접 확인합니다!

# 1. INSERT 직후의 ctid: (0,1) 와 UPDATE 직후의 ctid: (0,2) 비교!
kubectl exec -n part1-demo pg-client -- psql -h pg-vm.mesh.internal -U meshuser -d ordersdb `
  -c "TRUNCATE TABLE mesh_orders RESTART IDENTITY;" `
  -c "INSERT INTO mesh_orders (item) VALUES ('Order v1 - Initial');" `
  -c "SELECT ctid, xmin, xmax, id, item FROM mesh_orders;" `
  -c "UPDATE mesh_orders SET item = 'Order v2 - Updated!' WHERE id = 1;" `
  -c "SELECT ctid, xmin, xmax, id, item FROM mesh_orders;"

# 2. pg_stat_user_tables에서 죽은 튜플(n_dead_tup) 확인 후 VACUUM으로 청소!
Start-Sleep -Seconds 1
kubectl exec -n part1-demo pg-client -- psql -h pg-vm.mesh.internal -U meshuser -d ordersdb `
  -c "SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'mesh_orders';" `
  -c "VACUUM mesh_orders;" `
  -c "SELECT relname, n_live_tup, n_dead_tup, last_vacuum FROM pg_stat_user_tables WHERE relname = 'mesh_orders';"

실제 출력 결과:

TRUNCATE TABLE
INSERT 0 1
 ctid  | xmin | xmax | id |        item        
-------+------+------+----+--------------------
 (0,1) |  757 |    0 |  1 | Order v1 - Initial
(1 row)

UPDATE 1
 ctid  | xmin | xmax | id |        item         
-------+------+------+----+---------------------
 (0,2) |  758 |    0 |  1 | Order v2 - Updated!
(1 row)

   relname   | n_live_tup | n_dead_tup 
-------------+------------+------------
 mesh_orders |          1 |          1
(1 row)

VACUUM
   relname   | n_live_tup | n_dead_tup |          last_vacuum          
-------------+------------+------------+-------------------------------
 mesh_orders |          1 |          0 | 2026-10-25 07:27:27.555353+00
(1 row)

Step 3. Row-Level Lock(FOR UPDATE) 경합 재현 및 pg_blocking_pids() 범인 색출!

이번에는 실무 장애의 단골 주범인 행 잠금(Row Lock) 대기 현상을 직접 일으켜 봅니다.

1. 백그라운드 작업($job)으로 세션 1이 BEGIN; SELECT * FROM mesh_orders WHERE id = 1 FOR UPDATE; 로 1번 행에 자물쇠를 채운 뒤 6초 동안 COMMIT을 안 하고 버티게(pg_sleep(6)) 만듭니다.
2. 2초 뒤 또 다른 백그라운드 작업($job2)으로 세션 2가 똑같은 id = 1 행에 UPDATE를 시도하면 자물쇠에 막혀 그 자리에서 얼어붙습니다!
3. 그 순간 pg_stat_activity와 pg_blocking_pids(pid) 를 조회하여 현장을 덮칩니다!

# 1. [세션 1 - 범인] id=1 행을 FOR UPDATE로 잠그고 6초 동안 COMMIT 안 함!
$job = Start-Job -ScriptBlock {
  $env:Path += ";$HOME\bin"
  kubectl exec -n part1-demo pg-client -- psql -h pg-vm.mesh.internal -U meshuser -d ordersdb -c "BEGIN; SELECT * FROM mesh_orders WHERE id = 1 FOR UPDATE; SELECT pg_sleep(6); COMMIT;"
}
Start-Sleep -Seconds 2

# 2. [세션 2 - 피해자] id=1 행을 UPDATE 하려다 자물쇠에 걸려 멈춤!
$job2 = Start-Job -ScriptBlock {
  $env:Path += ";$HOME\bin"
  kubectl exec -n part1-demo pg-client -- psql -h pg-vm.mesh.internal -U meshuser -d ordersdb -c "UPDATE mesh_orders SET item = 'Blocked Update' WHERE id = 1;"
}
Start-Sleep -Seconds 2

# 3. [현장 검거] 누가(blocked_by) 누구(pid)를 막고 있는지 실시간 Lock 트리 조회!
kubectl exec -n part1-demo pg-client -- psql -h pg-vm.mesh.internal -U meshuser -d ordersdb -c "
SELECT pid, state, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocked_by, left(query, 55) AS query_snippet
FROM pg_stat_activity
WHERE datname = 'ordersdb' AND pid <> pg_backend_pid();
"

# 백그라운드 작업 종료 대기 및 정리
Wait-Job $job, $job2 | Out-Null
Remove-Job $job, $job2

실제 출력 결과:

 pid | state  | wait_event_type |  wait_event   | blocked_by |                      query_snippet                      
-----+--------+-----------------+---------------+------------+---------------------------------------------------------
  39 | active | Timeout         | PgSleep       | {}         | BEGIN; SELECT * FROM mesh_orders WHERE id = 1 FOR UPDAT
  40 | active | Lock            | transactionid | {39}       | UPDATE mesh_orders SET item = 'Blocked Update' WHERE id
(2 rows)

출력 결과 완벽 해부:

• pid: 40 (UPDATE mesh_orders ...):
  • wait_event_type: Lock / wait_event: transactionid: 현재 다른 트랜잭션이 쥐고 있는 자물쇠가 풀리기만을 애타게 기다리며 멈춰 서 있습니다!
  • blocked_by: {39}: pg_blocking_pids(40) 함수가 "지금 40번 프로세스의 멱살을 잡고 막고 있는 범인은 바로 39번 프로세스다!" 라고 정확히 지목해 주었습니다!
• pid: 39 (BEGIN; SELECT ... FOR UPDATE):
  • 자물쇠를 쥐고 PgSleep 중인 진짜 범인입니다.

Step 4. pg_stat_statements로 Lock 대기 시간까지 기록된 느린 쿼리 Top 5 조회

방금 pid 40번의 UPDATE 쿼리는 사실 데이터 1줄만 고치는 0.1ms짜리 아주 가벼운 쿼리였습니다. 그런데 앞선 pid 39번의 자물쇠 때문에 약 4초 동안 기다려야 했죠.
과연 pg_stat_statements 블랙박스에는 이 쿼리의 수행 시간이 어떻게 기록되었는지 확인해 봅니다!

kubectl exec -n part1-demo pg-client -- psql -h pg-vm.mesh.internal -U meshuser -d ordersdb -c "
SELECT calls, roundnumeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, rows, left(query, 60 AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;
"

실제 출력 결과:

 calls | total_ms | mean_ms | rows |                            query                             
-------+----------+---------+------+--------------------------------------------------------------
     1 |  6005.69 | 6005.69 |    1 | SELECT pg_sleep($1)
     2 |  4021.64 | 2010.82 |    2 | UPDATE mesh_orders SET item = $1 WHERE id = $2
     1 |    34.39 |   34.39 |    0 | CREATE EXTENSION IF NOT EXISTS pg_stat_statements
     1 |     3.45 |    3.45 |    0 | TRUNCATE TABLE mesh_orders RESTART IDENTITY
     1 |     0.17 |    0.17 |    1 | SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tab
(5 rows)

• UPDATE mesh_orders SET item = $1 WHERE id = $2 쿼리가 총 2번 호출(calls = 2)되었는데, 첫 번째(Step 2)는 0.1ms 만에 끝났지만 두 번째(Step 3)는 Lock 대기 시간 4초가 그대로 합산되어 total_ms = 4021.64ms (평균 2010.82ms)로 느린 쿼리 2위에 등극했습니다!
• 즉, pg_stat_statements에서 갑자기 단순 UPDATE 쿼리의 mean_ms가 수천 ms로 튀어 오른다면 그것은 인덱스 부족이 아니라 100% Row Lock 경합 때문임을 알 수 있습니다!

Step 5. 하이브리드 메시 인프라의 코드화 — Terraform (v1.11.0) 검증

마지막으로, 우리가 수동으로 만들었던 외부 VM 연동 리소스(WorkloadEntry)를 Terraform (hashicorp/kubernetes 프로바이더) 코드로 선언하고 문법과 스키마 무결성을 검증(terraform validate)합니다.

# 0. Terraform 설치 여부 확인 (미설치 시 $HOME\bin에 자동 설치)
$env:Path += ";$HOME\bin"
if (-not (Get-Command terraform -ErrorAction SilentlyContinue)) {
    curl.exe -sSLo "$HOME\bin\terraform.zip" "https://releases.hashicorp.com/terraform/1.11.0/terraform_1.11.0_windows_amd64.zip"
    Expand-Archive -Path "$HOME\bin\terraform.zip" -DestinationPath "$HOME\bin" -Force
    Remove-Item "$HOME\bin\terraform.zip"
}

# 1. Terraform 작업 디렉터리 생성 및 main.tf 작성
mkdir "$HOME\mesh-study\part7-terraform" -Force | Out-Null
@"
terraform {
  required_version = ">= 1.10.0"
  required_providers {
    kubernetes = {
      source  = "hashicorp/kubernetes"
      version = "~> 2.35"
    }
  }
}

provider "kubernetes" {
  config_path    = "~/.kube/config"
  config_context = "docker-desktop"
}

variable "external_pg_vm_ip" {
  description = "Private IP of the external PostgreSQL 18 Linux VM on the kind network"
  type        = string
  default     = "172.19.0.7"
}

resource "kubernetes_manifest" "external_pg_workload_entry" {
  manifest = {
    apiVersion = "networking.istio.io/v1"
    kind       = "WorkloadEntry"
    metadata = {
      name      = "study-pg-vm-01"
      namespace = "part1-demo"
    }
    spec = {
      address        = var.external_pg_vm_ip
      serviceAccount = "default"
      labels = {
        app      = "external-pg-vm"
        class    = "vm"
        instance = "study-pg-vm-01"
      }
    }
  }
}
"@ | Set-Content -Path "$HOME\mesh-study\part7-terraform\main.tf" -Encoding utf8

# 2. Terraform 프로바이더 초기화 및 코드 유효성 검증
cd "$HOME\mesh-study\part7-terraform"
terraform init -backend=false -no-color
terraform validate -no-color
cd "$HOME\mesh-study"

실제 출력 결과:

Initializing provider plugins...
- Finding hashicorp/kubernetes versions matching "~> 2.35"...
- Installing hashicorp/kubernetes v2.38.0...
- Installed hashicorp/kubernetes v2.38.0 (signed by HashiCorp)

Terraform has been successfully initialized!
Success! The configuration is valid.

Step 6. 자주 발생하는 문제와 해결 (Troubleshooting)

Step 7. 이번 파트 핵심 명령어 정리 (Command Reference)

-- 1. 테이블의 숨겨진 MVCC 물리 주소(ctid) 및 트랜잭션 번호(xmin, xmax) 조회
SELECT ctid, xmin, xmax, * FROM mesh_orders;

-- 2. 테이블별 살아있는 행(n_live_tup)과 죽은 행(n_dead_tup) 개수 확인
SELECT relname, n_live_tup, n_dead_tup, last_vacuum FROM pg_stat_user_tables WHERE relname = 'mesh_orders';

-- 3. 실시간 Lock 대기 세션 및 범인 프로세스(blocked_by) 1초 색출 쿼리
SELECT pid, state, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity WHERE datname = 'ordersdb' AND pid <> pg_backend_pid();

-- 4. 총 소요 시간 기준 상위 5개 느린 쿼리 조회
SELECT calls, roundnumeric, 2) AS total_ms, round(mean_exec_time::numeric, 2 AS mean_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;

Step 8. 셀프 체크 (Self-Check Q&A)

Q1. PostgreSQL에서 id = 1인 행을 UPDATE 했을 때 물리적 위치를 나타내는 ctid가 (0,1)에서 (0,2)로 바뀐 이유는 무엇인가요?

Q2. 서비스 응답이 갑자기 멈췄을 때 PostgreSQL에서 어떤 쿼리가 어떤 프로세스 때문에 Lock에 걸려 대기 중인지 즉시 찾아내는 시스템 뷰와 함수 이름은 무엇인가요?

🔗 다음 파트로 이동

이제 우리는 서비스 메시(ztunnel + Waypoint)부터 외부 데이터베이스(PostgreSQL 18)의 내부 구조까지 모두 장악했습니다!
다음 Part 08부터는 대망의 풀스택 LGTM 관측 아키텍처에 돌입합니다. 첫 번째 순서로 차세대 통합 수집기 Grafana Alloy 와 초고속 시계열 DB VictoriaMetrics(Mimir 호환) 를 구축하여 메시의 L4/L7 메트릭과 PostgreSQL 18 메트릭을 한곳으로 긁어모으는 실습을 진행합니다!

• 다음 노트: P08-1. 통합 수집기 Grafana Alloy와 Mimir/VictoriaMetrics (이론 개념)

네비게이션